package com.opennetexam.manager.sqlprovider;

import org.apache.log4j.Logger;

import java.util.HashMap;
import java.util.Map;

/**
 * Created on 2017/11/14.
 */
public class ExamSubjectProvider {

    private static final Logger logger = Logger.getLogger(ExamSubjectProvider.class);

    public String examSubjectFirstOrDefault(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examSubjectID = (String) parameter.get("examSubjectID");

        stringBuffer.append("SELECT * FROM ExamSubject ");
        stringBuffer.append("WHERE AppID = \'").append(appId).append("\' AND ExamPlanInfoID = ").append("\'").append(examPlanInfoID).append("\'").append(" AND ExamSubjectID = ").append("\'").append(examSubjectID).append("\' ").append(" LIMIT 1");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examPaperIsShuffled(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examSubjectID = (String) parameter.get("examSubjectID");

        stringBuffer.append("SELECT * FROM ExamSubject WHERE ExamSubjectID = \'").append(examSubjectID).append("\' LIMIT 1");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examSubjectMoniter(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appID = (String) parameter.get("appID");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examSceneID = (String) parameter.get("examSceneID");
        String examRoomID = (String) parameter.get("examRoomID");

        stringBuffer.append("SELECT DISTINCT x.* FROM ExamSubject AS x JOIN ");
        stringBuffer.append("( SELECT * FROM ExamineeArrange WHERE AppID = \'").append(appID).append("\'").append(" AND ExamPlanInfoID = \'").append(examPlanInfoID).append("\' AND ExamSceneID = \'").append(examSceneID).append("\' AND ExamRoomID = \'").append(examRoomID).append("\' LIMIT 1 ) AS y ");
        stringBuffer.append("ON x.ExamSubjectID = y.ExamSubjectID WHERE x.ExamPlanInfoID = y.ExamPlanInfoID AND x.AppID = y.AppID");


        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findExamSubjectListBySomeWhere(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String planId = (String) parameter.get("planId");
        String sceneId = (String) parameter.get("sceneId");
        String roomId = (String) parameter.get("roomId");

        sb.append("SELECT x.* FROM ExamSubject AS x JOIN ExamineeArrange AS y ON x.AppID = y.AppID AND y.ExamPlanInfoID=x.ExamPlanInfoID AND x.ExamSubjectID = y.ExamSubjectID WHERE 1=1");
        if(appId != null && !("").equals(appId)){
            sb.append(" AND y.AppID = \'").append(appId).append("\'");
        }
        if(planId != null){
            sb.append(" AND y.ExamPlanInfoID = \'").append(planId).append("\' ");
        }
        if(sceneId != null){
            sb.append(" AND y.ExamSceneID = \'").append(sceneId).append("\' ");
        }
        if(roomId != null){
            sb.append(" AND ExamRoomID = \'").append(roomId).append("\' ");
        }

        sb.append(" GROUP BY x.AppID, x.ExamPlanInfoID, x.ExamSubjectID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public static void main(String[] args){
        Map<String,Object> map = new HashMap<>();
        map.put("appId","1");
        map.put("planId",66);
        map.put("sceneId",154);
        map.put("roomId",21);
//        findExamSubjectListBySomeWhere(map);
    }
}
